#include "Rss_DB.h"
#include "StringCoder.h"

void Rss_DB::CreateDB(){
	Rss_DB db;
	if(db.Open()){
		if(db.SetSQL("CREATE TABLE IF NOT EXISTS Article (aid INTEGER PRIMARY KEY AUTOINCREMENT,feedId INTEGER,itemId INTEGER,unreadCount INTEGER,isSubscribe INTEGER,title TEXT,description TEXT,content TEXT)")){
			db.ExecuteNoQuery();
		}
		if(db.SetSQL("CREATE TABLE IF NOT EXISTS RssUser (uid TEXT,pwd TEXT)")){
			db.ExecuteNoQuery();
		}
	}
	db.Close();
}
Rss_DB::Rss_DB(){
	this->is_opened = false;
	this->is_prepared = false;
	this->db_name = "\\Disk\\Programs\\RssReader\\Rss_DATA";
}
Rss_DB::~Rss_DB(){
	this->Close();
}
bool Rss_DB::isInitial() const{
	return this->is_opened && this->is_prepared;
}
bool Rss_DB::Open(){
	if(this->is_opened) return true;
	int retcode = 0;
	retcode = sqlite3_open(this->db_name.c_str(), &this->p_db);
	if (retcode != SQLITE_OK){
		cerr<<"Could not open " << this->db_name << ":" << sqlite3_errmsg(this->p_db);
		sqlite3_close(this->p_db);
		this->is_opened = false;
		return false;
	}
	this->is_opened = true;
	return true;
}
void Rss_DB::Close(){
	if(this->is_prepared){
		sqlite3_finalize(this->p_stmt);
		this->is_prepared = false;
	}
	if(this->is_opened){
		sqlite3_close(this->p_db);
		this->is_opened = false;
	}
}
bool Rss_DB::SetSQL(const string& sql){
	if(this->is_prepared){
		sqlite3_finalize(this->p_stmt);
	}
	int retcode = 0;
	retcode = sqlite3_prepare(this->p_db,sql.c_str(),-1,&this->p_stmt,0);
	if (retcode != SQLITE_OK){
		cerr<<"Could not prepare "<< sql << ":" << sqlite3_errmsg(this->p_db) << endl;
		this->is_prepared = false;
		return false;
	}
	this->is_prepared = true;
	return true;
}
bool Rss_DB::ExecuteNoQuery(){
	if(!this->isInitial()){
		cerr<<"you must initial the db first."<<endl;
		return false;
	}
	if(SQLITE_DONE != sqlite3_step(this->p_stmt)){
		const char* p = sqlite3_errmsg(this->p_db);
		cerr<<"Execute error:"<< p << endl;
		return false;
	}
	return true;
}
string Rss_DB::ExecuteScalar(){
	string val;
	if(!this->isInitial()){
		cerr<<"you must initial the db first."<<endl;
		return val;
	}
	if(SQLITE_ROW != sqlite3_step(this->p_stmt)){
		cerr<<"Execute error:"<< sqlite3_errmsg(this->p_db) << endl;
		return val;
	}
	const unsigned char* ret = sqlite3_column_text(this->p_stmt,0);
	const char* ret2 = (const char*) ret;
	return string(ret2);
}
list<MyListItemData> Rss_DB::GetItemData_Page(const string& sql,int page,Rss_PageInfo* pageInfo,int pageSize){
	list<MyListItemData> itemList;
	Rss_DB db1,db2;
	string countstr = string("SELECT COUNT(*) FROM ( ")+ sql + string(") AS countTable");
	if(!db1.Open() || !db1.SetSQL(countstr)){
		cerr <<  "database open error or set sql error:" << countstr << endl;
		return itemList;
	}
	pageInfo->RecordCount = stdex::atoi(db1.ExecuteScalar().c_str());
	db1.Close();
	pageInfo->PageSize = pageSize;
	pageInfo->CurrentPage = page;
	pageInfo->Caculate();

	string str = sql + " LIMIT " + stdex::itoa(pageInfo->GetValidStartIndex()) + "," + stdex::itoa(pageSize);
	if(!db2.Open() || !db2.SetSQL(str)){
		cerr <<  "database open error or set sql error:" << str << endl;
		return itemList;
	}
	while(SQLITE_ROW == sqlite3_step(db2.p_stmt)){
		MyListItemData item;
		item.IsSubcribe = true;
		item.UnreadCount = 0;
		int column_count = sqlite3_column_count(db2.p_stmt);
		for(int i=0;i<column_count;i++){
			const unsigned char* p_str = NULL;
			string columnName = sqlite3_column_name(db2.p_stmt,i);
			if(columnName == "title"){
				p_str = sqlite3_column_text(db2.p_stmt,i);
				if(p_str)
					item.StringTitle = StringCoder::StringToWString(string((const char*)p_str)).c_str();
			}
			else if(columnName == "itemId"){
				item.itemId = sqlite3_column_int(db2.p_stmt,i);
			}
			else if(columnName == "description"){
				p_str = sqlite3_column_text(db2.p_stmt,i);
				if(p_str)
					item.StringDescription = StringCoder::StringToWString(string((const char*)p_str)).c_str();
			}
			else if(columnName == "content"){
				p_str = sqlite3_column_text(db2.p_stmt,i);
				if(p_str)
					item.itemContent = StringCoder::StringToWString(string((const char*)p_str)).c_str();
			}
			else if(columnName == "feedId"){
				item.feedId = sqlite3_column_int(db2.p_stmt,i);
			}
			else if(columnName == "unreadCount"){
				item.UnreadCount = sqlite3_column_int(db2.p_stmt,i);
			}
			else if(columnName == "isSubscribe"){
				item.IsSubcribe = sqlite3_column_int(db2.p_stmt,i);
			}
		}
		itemList.push_back(item);
	}
	db2.Close();
	return itemList;
}

void Rss_DB::DeleteArticle(int itemId)
{
	string sql = "DELETE FROM Article WHERE itemId=";
	sql += stdex::itoa(itemId);	
	Rss_DB db;
	if(db.Open()){
		db.SetSQL(sql);
		db.ExecuteNoQuery();
		db.Close();
	}
}

int Rss_DB::TotalArticle()
{
	string sql = "SELECT COUNT(*) FROM Article";
	Rss_DB db;
	if(db.Open())
		if(db.SetSQL(sql))
			return stdex::atoi(db.ExecuteScalar());
	return -1;
}

bool Rss_DB::ArticleExists(int itemId)
{
	string sql = "SELECT COUNT(*) FROM Article WHERE itemId=";
	sql += stdex::itoa(itemId);	
	Rss_DB db;
	if(db.Open())
		if(db.SetSQL(sql))
			return stdex::atoi(db.ExecuteScalar());
	return 0;
}

void Rss_DB::SaveArticle(const MyListItemData* p_data)
{
	string sql = "SELECT COUNT(*) FROM Article WHERE itemId=";
	sql += stdex::itoa(p_data->itemId);	
	Rss_DB db;
	if(db.Open()){
		db.SetSQL(sql);
		if(!stdex::atoi(db.ExecuteScalar()))
		{
			sql = "INSERT INTO Article(feedId,itemId,unreadCount,isSubscribe,title,description,content) VALUES (?,?,?,?,?,?,?)";
			db.SetSQL(sql);
			sqlite3_bind_int(db.p_stmt,1,p_data->feedId);
			sqlite3_bind_int(db.p_stmt,2,p_data->itemId);
			sqlite3_bind_int(db.p_stmt,3,p_data->UnreadCount);
			sqlite3_bind_int(db.p_stmt,4,p_data->IsSubcribe);
			sqlite3_bind_text(db.p_stmt,5,StringCoder::WstringToString(p_data->StringTitle).c_str(),-1,SQLITE_TRANSIENT);
			sqlite3_bind_text(db.p_stmt,6,StringCoder::WstringToString(p_data->StringDescription).c_str(),-1,SQLITE_TRANSIENT);
			sqlite3_bind_text(db.p_stmt,7,StringCoder::WstringToString(p_data->itemContent).c_str(),-1,SQLITE_TRANSIENT);
			db.ExecuteNoQuery();
		}
	}
	db.Close();
}
wstring Rss_DB::GetUser()
{
	string sql = "SELECT uid FROM RssUser";
	Rss_DB db;
	if(db.Open())
		if(db.SetSQL(sql))
			return StringCoder::StringToWString(db.ExecuteScalar());
	return L"";
}
wstring Rss_DB::GetPwd()
{
	string sql = "SELECT pwd FROM RssUser";
	Rss_DB db;
	if(db.Open())
		if(db.SetSQL(sql))
			return StringCoder::StringToWString(db.ExecuteScalar());
	return L"";
}
void Rss_DB::SaveUser(const wstring& user,const wstring& pwd)
{
	string sql = "DELETE FROM RssUser";
	Rss_DB db;
	if(db.Open()){
		if(db.SetSQL(sql) && db.ExecuteNoQuery())
		{
			sql = "INSERT INTO RssUser(uid,pwd) VALUES (?,?)";
			db.SetSQL(sql);
			sqlite3_bind_text(db.p_stmt,1,StringCoder::WstringToString(user).c_str(),-1,SQLITE_TRANSIENT);
			sqlite3_bind_text(db.p_stmt,2,StringCoder::WstringToString(pwd).c_str(),-1,SQLITE_TRANSIENT);
			db.ExecuteNoQuery();
		}
		db.Close();
	}
}